package manager;

import java.io.IOException;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.JspWriter;

import org.apache.jasper.runtime.JspWriterImpl;

import utils.DBConnector;
import utils.Printer;

/**
 * Servlet implementation class SearchCourse
 */
public class SearchCourse extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchCourse() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getSession(false) == null || request.getSession(false).getAttribute("username") == null){
			response.sendError(HttpServletResponse.SC_FORBIDDEN, "Please login first");
			return;
		}
		DBConnector dbConn = null;
		try{ 
			dbConn = new DBConnector();
			JspWriter out = new JspWriterImpl(response);
			boolean isEmpty;
	
			PreparedStatement stmnt = dbConn.getConnection().prepareStatement(
						"SELECT course_name, credit_points, group_number, (capacity - (SELECT COUNT(username) FROM user_courses WHERE course_name = c.course_name)) " 		+  
						"FROM courses c " 												+  
						"WHERE is_active = TRUE AND" 									+ 
						"    (c.course_name   like concat('%', ?, '%') or ? = '' ) "	+
						"and (c.description   like concat('%', ?, '%') or ? = '' ) "	+
						"and (c.group_number  = ? or ? = '')  "							+
						"and (c.credit_points = ? or ? = '') "							+ 
						"and (c.course_name, ? ) not in "								+ 
						"(select uc.course_name, uc.username from user_courses uc); "
						);
	
			int i = 1;
			stmnt.setString(i++, request.getParameter("course_name"));
			stmnt.setString(i++, request.getParameter("course_name"));
			stmnt.setString(i++, request.getParameter("course_description"));
			stmnt.setString(i++, request.getParameter("course_description"));
			stmnt.setString(i++, request.getParameter("course_group"));
			stmnt.setString(i++, request.getParameter("course_group"));
			stmnt.setString(i++, request.getParameter("credit_points"));
			stmnt.setString(i++, request.getParameter("credit_points"));
			String username = request.getSession(false).getAttribute("username").toString();
			stmnt.setString(i++, username);
	
	
			ResultSet res = stmnt.executeQuery();
			isEmpty = !res.next(); //will be true if reultset is empty
			res.beforeFirst();
			
			if (isEmpty){
				out.println("<h2>Sorry, no matches...</h2>");
				out.close();
			}
	
			else {
				out.println("<h2>Results</h2>");
				
				Printer.printCourses(out, res, "AddCourseUser", "user_course_details.jsp", "Register to Course", true, false, username );
				out.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "Sorry...");
			return;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			try {
				dbConn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}
